﻿using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Sofire.Data;

namespace DataTestProject
{
    public class MyUser : IModelLoadable
    {
        public string Username { get; set; }
        public string Password { get; set; }

        #region IModelLoadable 成员

        bool IModelLoadable.Load(System.Data.IDataReader reader)
        {
            Username = reader.GetString(reader.GetOrdinal("Username"));
            Password = reader.GetString(reader.GetOrdinal("Password"));
            return true;
        }

        #endregion
    }

    /// <summary>
    /// ExecuteTableTest 的摘要说明
    /// </summary>
    [TestClass]
    public class DataDemoTest
    {
        public DataDemoTest()
        {
            //
            //TODO: 在此处添加构造函数逻辑
            //
        }

        private TestContext testContextInstance;

        /// <summary>
        ///获取或设置测试上下文，该上下文提供
        ///有关当前测试运行及其功能的信息。
        ///</summary>
        public TestContext TestContext
        {
            get
            {
                return testContextInstance;
            }
            set
            {
                testContextInstance = value;
            }
        }

        #region 附加测试特性
        //
        // 编写测试时，可以使用以下附加特性:
        //
        // 在运行类中的第一个测试之前使用 ClassInitialize 运行代码
        // [ClassInitialize()]
        // public static void MyClassInitialize(TestContext testContext) { }
        //
        // 在类中的所有测试都已运行之后使用 ClassCleanup 运行代码
        // [ClassCleanup()]
        // public static void MyClassCleanup() { }
        //
        // 在运行每个测试之前，使用 TestInitialize 来运行代码
        // [TestInitialize()]
        // public void MyTestInitialize() { }
        //
        // 在每个测试运行完之后，使用 TestCleanup 来运行代码
        // [TestCleanup()]
        // public void MyTestCleanup() { }
        //
        #endregion

        [TestMethod]
        public void Test000_CreateTable()
        {
            var t = Share.Engine.ExecuteNonQuery("CREATE TABLE MyUsers(Username varchar(20) PRIMARY KEY,password varchar(20))");

            Assert.IsTrue(t.IsSucceed, "创建 MyUsers 表");
        }

        [TestMethod]
        public void Test001_ExecuteDemo1ByContext()
        {
            QueryContext context;
            using(context = Share.Engine.Context)
            {
                var r1 = context.ExecuteNonQuery("INSERT INTO MyUsers(Username,Password) VALUES(@uid,@pwd)"
                     , "@uid", "user1"
                     , "@pwd", "123456");
                Assert.IsTrue(r1.IsSucceed, "插入用户1");
                r1 = context.ExecuteNonQuery("INSERT INTO MyUsers(Username,Password) VALUES(@uid,@pwd)"
                      , "@uid", "user2"
                      , "@pwd", "123456");
                Assert.IsTrue(r1.IsSucceed, "插入用户2");

                var r2 = context.ExecuteScalar("SELECT COUNT(*) FROM MyUsers");
                Assert.IsTrue(r2.IsSucceed, "查询用户");
                Assert.AreEqual(2L, r2.Value, "查询出用户数量共有 2 个");

                Assert.IsFalse(context.IsClosed, "数据库正常连接");
            }
            Assert.IsTrue(context.IsClosed, "数据库已关闭连接");
        }

        [TestMethod]
        public void Test002_ExecuteDemo1()
        {
            QueryEngineBase query = Share.Engine;
            var r1 = query.ExecuteNonQuery("INSERT INTO MyUsers(Username,Password) VALUES(@uid,@pwd)"
                 , "@uid", "user3"
                 , "@pwd", "123456");
            Assert.IsTrue(r1.IsSucceed, "插入用户3");
            r1 = query.ExecuteNonQuery("INSERT INTO MyUsers(Username,Password) VALUES(@uid,@pwd)"
                  , "@uid", "user4"
                  , "@pwd", "123456");
            Assert.IsTrue(r1.IsSucceed, "插入用户4");

            var r2 = query.ExecuteScalar("SELECT COUNT(*) FROM MyUsers");
            Assert.IsTrue(r2.IsSucceed, "查询用户");
            Assert.AreEqual(4L, r2.Value, "查询出用户数量共有 4 个");
        }

        [TestMethod]
        public void Test003_ExecuteReader()
        {
            using(var context = Share.Engine.Context)
            {
                int x = 0;
                context.ExecuteReader((r) =>
                {
                    while(r.Read())
                    {
                        x++;
                    }
                }, "SELECT * FROM MyUsers");
                Assert.AreEqual(4, x, "Reader 查询出用户数量共有 4 个");
            }
        }

        [TestMethod]
        public void Test004_ExecuteMultiNoQuery_Test()
        {
            using(var context = Share.Engine.Context)
            {
                List<ExecuteCommand> commands = new List<ExecuteCommand>(6);
                for(int i = 5 ; i < 11 ; i++)
                {
                    commands.Add(new ExecuteCommand("INSERT INTO MyUsers(Username,Password) VALUES(@uid,@pwd)"
                                                     , "@uid", "user" + i
                                                     , "@pwd", "123456"));
                }
                var r = context.ExecuteNonQuery(commands);
                Assert.IsTrue(r.IsSucceed, "插入 6 个用户。");
                Assert.AreEqual(10L, context.ExecuteScalar("SELECT COUNT(*) FROM MyUsers").Value, "共有 10 个用户。");
            }
        }

        [TestMethod]
        public void Test005_ExecuteTransaction()
        {
            using(var context = Share.Engine.Context)
            {
                List<ExecuteCommand> commands = new List<ExecuteCommand>(6);
                for(int i = 11 ; i < 21 ; i++)
                {
                    commands.Add(new ExecuteCommand("INSERT INTO MyUsers(Username,Password) VALUES(@uid,@pwd)"
                                                     , "@uid", "user" + i
                                                     , "@pwd", "123456"));
                }
                var r = context.ExecuteTransaction(commands);
                Assert.IsTrue(r.IsSucceed, "插入 10 个用户。");
                Assert.AreEqual(20L, context.ExecuteScalar("SELECT COUNT(*) FROM MyUsers").Value, "共有 20 个用户。");
            }
        }

        [TestMethod]
        public void Test006_ExecuteModel()
        {
            using(var context = Share.Engine.Context)
            {
                var r = context.ExecuteModel<MyUser>("SELECT * FROM MyUsers WHERE Username='user1'");
                Assert.IsTrue(r.IsSucceed, "查询用户");
                Assert.AreEqual("user1", r.Value.Username, "用户名为 user1");
            }
        }

        [TestMethod]
        public void Test007_ExecuteModels()
        {
            using(var context = Share.Engine.Context)
            {
                var r = context.ExecuteModels<MyUser>("SELECT * FROM MyUsers");

                Assert.IsTrue(r.IsSucceed, "查询用户");
                Assert.AreEqual(20, r.Value.Count, "共有 20 个用户。");
            }
        }

        [TestMethod]
        public void Test008_ExecuteEntity()
        {
            using(var context = Share.Engine.Context)
            {
                var r = context.ExecuteEntity<MyUser>("SELECT * FROM MyUsers WHERE Username='user1'");

                Assert.IsTrue(r.IsSucceed, "查询用户");
                Assert.AreEqual("user1", r.Value.Username, "用户名为 user1");

                var newPassword = "123456_new1";
                r.Value.Password = newPassword;
                var i = r.Save();

                Assert.AreEqual(1, i, "修改 user1 的密码");

                Assert.AreEqual(newPassword, context.ExecuteScalar("SELECT Password FROM MyUsers WHERE Username='user1'").Value, "user1 密码修改为 " + newPassword);

            }
        }

        [TestMethod]
        public void Test009_ExecuteEntities()
        {
            using(var context = Share.Engine.Context)
            {
                var r = context.ExecuteEntities<MyUser>("SELECT * FROM MyUsers ORDER BY Username");

                Assert.IsTrue(r.IsSucceed, "查询用户");
                var username = r.Value[1].Username;
                var newPassword = "123456_" + username;
                r.Value[1].Password = newPassword;
                var i = r.Save();
                Assert.AreEqual(1, i, "修改 user2 的密码");

                Assert.AreEqual(newPassword, context.ExecuteScalar("SELECT Password FROM MyUsers WHERE Username=@uid", "@uid", username).Value, username + " 密码修改为 " + newPassword);

            }
        }

        [TestMethod]
        public void Test010_ExecuteExecuteTransactionByValues()
        {
            using(var context = Share.Engine.Context)
            {
                ExecuteCommand command = new ExecuteCommand("INSERT INTO MyUsers(Username,Password) VALUES(@uid,@pwd)"
                                                     , "@uid", DBNull.Value
                                                     , "@pwd", DBNull.Value);
                object[][] values = new object[5][];
                for(int i = 0 ; i < 5 ; i++)
                {
                    values[i] = new object[2] { "userUID" + i, "userPWD" + i };
                }
                var r = context.ExecuteValues(command, values);
                Assert.IsTrue(r.IsSucceed, "添加 5 个用户");
                Assert.AreEqual(25L, context.ExecuteScalar("SELECT COUNT(*) FROM MyUsers").Value, "共有 25 个用户。");
            }
        }


        [TestMethod]
        public void Test011_ExecuteExecuteTransaction()
        {
            using(var context = Share.Engine.Context)
            {
                context.OpenTransaction();

                for(int i = 0 ; i < 10 ; i++)
                {
                    context.ExecuteNonQuery("INSERT INTO MyUsers(Username,Password) VALUES(@uid,@pwd)"
                                                      , "@uid", "userUIDXX" + i
                                                      , "@pwd", "userPWDXX");
                }
                Assert.AreEqual(25L, Share.Engine.ExecuteScalar("SELECT COUNT(*) FROM MyUsers").Value, "共有 25 个用户。");
                context.Commit();
                Assert.AreEqual(35L, context.ExecuteScalar("SELECT COUNT(*) FROM MyUsers").Value, "共有 35 个用户。");
            }
        }
    }
}
